﻿Imports System.Text

Namespace StatementSources
    Public Class CreateScheduleStatementSource
        Inherits BaseStatementSource

        Public Sub New(employeeId As Integer, _
                       subjectId As Integer, _
                       roomNumber As String, _
                       scheduleDay As String, _
                       startDate As DateTime, _
                       endDate As DateTime, _
                       startTime As DateTime, _
                       endTime As DateTime, _
                       semester As String, _
                       createdBy As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("INSERT [dbo].[Schedules] ")
            stringBuilder.Append("([EmployeeId],[SubjectId],[RoomNumber],[ScheduleDay],[StartDate],[EndDate],[StartTime],[EndTime],[Semester],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm]) ")
            stringBuilder.Append("VALUES (@EmployeeId,@SubjectId,@RoomNumber,@ScheduleDay,@StartDate,@EndDate,@StartTime,@EndTime,@Semester,@CreatedBy,GETDATE(),@CreatedBy,GETDATE());")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@EmployeeId", employeeId)
            Parameters.AddWithValue("@SubjectId", subjectId)
            Parameters.AddWithValue("@RoomNumber", roomNumber)
            Parameters.AddWithValue("@ScheduleDay", scheduleDay)
            Parameters.AddWithValue("@StartDate", startDate)
            Parameters.AddWithValue("@EndDate", endDate)
            Parameters.AddWithValue("@StartTime", startTime)
            Parameters.AddWithValue("@EndTime", endTime)
            Parameters.AddWithValue("@Semester", semester)
            Parameters.AddWithValue("@CreatedBy", createdBy)
        End Sub

    End Class

    Public Class ReadAllScheduleStatementSource
        Inherits BaseStatementSource

        Public Sub New()
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [ScheduleId],[EmployeeId],[SubjectId],[RoomNumber],[ScheduleDay],[StartDate],[EndDate],[StartTime],[EndTime],[Semester],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Schedules];")

            Sql = stringBuilder.ToString()
        End Sub

    End Class

    Public Class ReadAllScheduleByInstructorIdStatementSource
        Inherits BaseStatementSource

        Public Sub New(instructorId As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [ScheduleId],[EmployeeId],[SubjectId],[RoomNumber],[ScheduleDay],[StartDate],[EndDate],[StartTime],[EndTime],[Semester],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Schedules] ")
            stringBuilder.Append("WHERE [EmployeeId]=@EmployeeId;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@EmployeeId", instructorId)
        End Sub

        Public Sub New(instructorId As Integer, semester As String)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [ScheduleId],[EmployeeId],[SubjectId],[RoomNumber],[ScheduleDay],[StartDate],[EndDate],[StartTime],[EndTime],[Semester],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Schedules] ")
            stringBuilder.Append("WHERE [EmployeeId]=@EmployeeId ")
            stringBuilder.Append("AND [Semester]=@Semester;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@EmployeeId", instructorId)
            Parameters.AddWithValue("@Semester", semester)
        End Sub

    End Class

    Public Class ReadByScheduleIdStatementSource
        Inherits BaseStatementSource

        Public Sub New(scheduleId As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("SELECT [ScheduleId],[EmployeeId],[SubjectId],[RoomNumber],[ScheduleDay],[StartDate],[EndDate],[StartTime],[EndTime],[Semester],[CreatedBy],[CreatedDttm],[ModifiedBy],[ModifiedDttm] ")
            stringBuilder.Append("FROM [dbo].[Schedules] ")
            stringBuilder.Append("WHERE [ScheduleId]=@ScheduleId;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@ScheduleId", scheduleId)
        End Sub

    End Class

    Public Class UpdateScheduleStatementSource
        Inherits BaseStatementSource

        Public Sub New(scheduleId As Integer, _
                       employeeId As Integer, _
                       subjectId As Integer, _
                       scheduleDay As String, _
                       roomNumber As String, _
                       startDate As DateTime, _
                       endDate As DateTime, _
                       startTime As DateTime, _
                       endTime As DateTime, _
                       semester As String, _
                       modifiedBy As Integer)
            Dim stringBuilder As New StringBuilder()
            stringBuilder.Append("UPDATE [dbo].[Schedules] SET ")
            stringBuilder.Append("[EmployeeId]=ISNULL(@EmployeeId,[EmployeeId]),")
            stringBuilder.Append("[SubjectId]=ISNULL(@SubjectId,[SubjectId]),")
            stringBuilder.Append("[RoomNumber]=ISNULL(@RoomNumber,[RoomNumber]),")
            stringBuilder.Append("[ScheduleDay]=ISNULL(@ScheduleDay,[ScheduleDay]),")
            stringBuilder.Append("[StartDate]=ISNULL(@StartDate,[StartDate]),")
            stringBuilder.Append("[EndDate]=ISNULL(@EndDate,[EndDate]),")
            stringBuilder.Append("[StartTime]=ISNULL(@StartTime,[StartTime]),")
            stringBuilder.Append("[EndTime]=ISNULL(@EndTime,[EndTime]),")
            stringBuilder.Append("[Semester]=ISNULL(@Semester,[Semester]),")
            stringBuilder.Append("[ModifiedBy]=@ModifiedBy,")
            stringBuilder.Append("[ModifiedDttm]=GETDATE() ")
            stringBuilder.Append("WHERE [ScheduleId]=@ScheduleId;")

            Sql = stringBuilder.ToString()

            Parameters.AddWithValue("@ScheduleId", scheduleId)
            Parameters.AddWithValue("@EmployeeId", employeeId)
            Parameters.AddWithValue("@SubjectId", subjectId)
            Parameters.AddWithValue("@RoomNumber", roomNumber)
            Parameters.AddWithValue("@ScheduleDay", scheduleDay)
            Parameters.AddWithValue("@StartDate", startDate)
            Parameters.AddWithValue("@EndDate", endDate)
            Parameters.AddWithValue("@StartTime", startTime)
            Parameters.AddWithValue("@EndTime", endTime)
            Parameters.AddWithValue("@Semester", semester)
            Parameters.AddWithValue("@ModifiedBy", modifiedBy)
        End Sub

    End Class

    Public Class DeleteScheduleStatementSource
        Inherits BaseStatementSource

        Public Sub New(scheduleId As Integer)
            Sql = "DELETE FROM [dbo].[Schedules] WHERE [ScheduleId]=@ScheduleId;"
            Parameters.AddWithValue("@ScheduleId", scheduleId)
        End Sub

    End Class

End Namespace

